import pandas as pd
import os
from pathlib import Path
import numpy as np

def should_process_sheet(sheet_name):
    excluded_terms = ['Component', 'mdlCalcs']
    return not any(term.lower() in sheet_name.lower() for term in excluded_terms)

def process_single_excel(input_file):
    # Read all sheets from the Excel file
    excel_file = pd.ExcelFile(input_file)
    sheet_names = excel_file.sheet_names
    
    # Filter out Component and mdlCalcs sheets
    valid_sheets = [sheet for sheet in sheet_names if should_process_sheet(sheet)]
    
    # Initialize empty DataFrames for both worksheets
    area_df = pd.DataFrame()
    amount_df = pd.DataFrame()
    
    try:
        # Process the first sheet to get the 'Filename' column
        first_sheet = pd.read_excel(input_file, sheet_name=valid_sheets[0])
        # Get the first column from row 6 to 6th to last row
        filenames = first_sheet.iloc[5:-6, 0]
        
        # Add Filename column to both DataFrames
        area_df['Filename'] = filenames.reset_index(drop=True)
        amount_df['Filename'] = filenames.reset_index(drop=True)
        
        # Process each valid sheet
        for sheet_name in valid_sheets:
            # Read the sheet
            df = pd.read_excel(input_file, sheet_name=sheet_name)
            
            # Extract and clean the fifth column (Area) from row 6 to 6th to last row
            area_data = df.iloc[5:-6, 4]
            
            # Extract and clean the ninth column (Amount) from row 6 to 6th to last row
            amount_data = df.iloc[5:-6, 8]
         
            # Add the columns to respective DataFrames with just the sheet name
            area_df[sheet_name] = area_data.reset_index(drop=True)
            amount_df[sheet_name] = amount_data.reset_index(drop=True)
            
        print(f"Processing {os.path.basename(input_file)}:")
        print(f"  Total sheets: {len(sheet_names)}")
        print(f"  Processed sheets: {len(valid_sheets)}")
        print(f"  Excluded sheets: {[s for s in sheet_names if not should_process_sheet(s)]}")
            
    except Exception as e:
        print(f"Error processing file {input_file}: {str(e)}")
        return None, None
        
    return area_df, amount_df

def merge_dataframes(dfs):
    """Merge multiple DataFrames keeping only one set of Filenames"""
    if not dfs:
        return None
    
    # Use the first DataFrame as base (for Filename column)
    merged_df = dfs[0].copy()
    
    # For each additional DataFrame
    for df in dfs[1:]:
        # Get only the data columns (excluding 'Filename')
        data_columns = [col for col in df.columns if col != 'Filename']
        # Add these columns to the merged DataFrame
        for col in data_columns:
            merged_df[col] = df[col]
    
    return merged_df

def process_folder(input_folder, output_file):
    """Process all Excel files in the folder and create merged output"""
    # Get all Excel files in the folder
    excel_files = [f for f in os.listdir(input_folder) if f.endswith(('.xlsx', '.xls', '.XLS'))]
    
    if not excel_files:
        print("No Excel files found in the specified folder.")
        return
    
    # Lists to store DataFrames for merging
    all_area_dfs = []
    all_amount_dfs = []
    
    # Process each Excel file
    for excel_file in excel_files:
        file_path = os.path.join(input_folder, excel_file)
        print(f"\nProcessing {excel_file}...")
        
        area_df, amount_df = process_single_excel(file_path)
        
        if area_df is not None and amount_df is not None:
            all_area_dfs.append(area_df)
            all_amount_dfs.append(amount_df)
    
    if not all_area_dfs or not all_amount_dfs:
        print("No valid data found to merge.")
        return
    
    try:
        # Merge Area and Amount DataFrames
        merged_area = merge_dataframes(all_area_dfs)
        merged_amount = merge_dataframes(all_amount_dfs)
        
        # Save to output file with multiple sheets
        with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
            merged_area.to_excel(writer, sheet_name='Area', index=False)
            merged_amount.to_excel(writer, sheet_name='Amount', index=False)
            
        print(f"\nSuccessfully created merged file: {output_file}")
        print(f"Area worksheet columns: {merged_area.columns.tolist()}")
        print(f"Amount worksheet columns: {merged_amount.columns.tolist()}")
        
    except Exception as e:
        print(f"Error while merging files: {str(e)}")

# input_folder = "path/to/your/excel/files"
# output_file = "merged_output.xlsx"
# process_folder(input_folder, output_file)
# process_folder("./婧秋师姐_数据\Raw/12262024/2/甲醇提取", "2_甲醇提取.xlsx")
process_folder("./婧秋师姐_数据\Raw/12262024/2\乙酸乙酯提取", "2_乙酸乙酯提取.xlsx")